﻿using System;
using System.Data;
using TugasAkhir.Helpers;
using TugasAkhir.Objects;

namespace TugasAkhir.Models
{
    public static class PertanyaanModel
    {
        public static bool GetId(out string id)
        {
            var hasilQuery = new DataSet();
            string query = "SELECT TOP 1 [id_pertanyaan] FROM [dbo].[pertanyaan] ORDER BY [id_pertanyaan] DESC";
            bool flag = SqlServer.ExecuteReader(query, out hasilQuery);

            if (flag)
            {
                if (hasilQuery.Tables[0] != null && hasilQuery.Tables[0].Rows.Count > 0)
                {
                    id = hasilQuery.Tables[0].Rows[0][0].ToString();
                    string idBaru = (int.Parse(id.Substring(1)) + 1).ToString();
                    while (idBaru.Length < 2) idBaru = "0" + idBaru;

                    id = "P" + idBaru;

                    return true;
                }
                else
                {
                    id = "error";
                    return false;
                }
            }
            else
            {
                id = "error";
                return false;
            }
        }

        public static bool CekId(string id)
        {
            var hasilQuery = new DataSet();
            string query = String.Format("SELECT [id_pertanyaan] FROM [dbo].[pertanyaan] WHERE [id_pertanyaan] = '{0}'", id);
            bool flag = SqlServer.ExecuteReader(query, out hasilQuery);

            if (flag)
            {
                if (hasilQuery.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public static bool GetSemuaPertanyaan(out DataSet hasilQuery)
        {
            string query = "SELECT [id_pertanyaan] ,CONVERT(VARCHAR(MAX), [pertanyaan]) AS pertanyaan FROM [dbo].[pertanyaan] ORDER BY [pertanyaan] ASC";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool Pencarian(string kataKunci, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT [id_pertanyaan] ,CONVERT(VARCHAR(MAX), [pertanyaan]) AS pertanyaan FROM [dbo].[pertanyaan] WHERE [pertanyaan] LIKE '%{0}%' ORDER BY [pertanyaan] ASC", kataKunci);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetDetailPertanyaan(string id, out PertanyaanObject pertanyaan)
        {
            var hasilQuery = new DataSet();
            string query = String.Format("SELECT [id_pertanyaan] ,[pertanyaan] FROM [dbo].[pertanyaan] WHERE [id_pertanyaan] = '{0}'", id);

            pertanyaan = new PertanyaanObject();
            bool flag = SqlServer.ExecuteReader(query, out hasilQuery);
            if (flag)
            {
                var data = hasilQuery.Tables[0].Rows[0];
                pertanyaan.IdPertanyaan = data[0].ToString();
                pertanyaan.Pertanyaan = data[1].ToString();
            }
            return flag;
        }

        public static bool TambahPertanyaan(PertanyaanObject pertanyaan)
        {
            string query = String.Format("INSERT INTO [dbo].[pertanyaan] ([id_pertanyaan] ,[pertanyaan]) VALUES ('{0}' ,'{1}')", pertanyaan.IdPertanyaan, pertanyaan.Pertanyaan);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool UbahPertanyaan(PertanyaanObject pertanyaan)
        {
            string query = String.Format("UPDATE [dbo].[pertanyaan] SET [pertanyaan] = '{0}' WHERE [id_pertanyaan] = '{1}'", pertanyaan.Pertanyaan, pertanyaan.IdPertanyaan);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool HapusPertanyaan(string id)
        {
            string query = String.Format("DELETE FROM [dbo].[pertanyaan] WHERE [id_pertanyaan] = '{0}'", id);
            return SqlServer.ExecuteNonQuery(query);
        }
    }
}